﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SystemDirectory.Common;
using SystemDirectory.Entity;

namespace SystemDirectory.DAO
{
    public class DAOMinor
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString2"].ToString());         
        SqlCommand com;
        SqlDataAdapter adapt;
        DataTable dtResult;

        public DataTable Get(bool ShowInactive, Utilities.SearchType IsSearch)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            switch (IsSearch)
            {
                case Utilities.SearchType.TypeAll:
                    {
                        if (ShowInactive)
                            com = new SqlCommand("SELECT * FROM MinorWork", con);
                        else
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE IsActive = 'true'", con);
                        break;
                    }
                case Utilities.SearchType.Type09:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE '0%' OR Description LIKE '1%' OR Description LIKE '2%' OR Description LIKE '3%' OR Description LIKE '4%'OR Description LIKE '5%' OR Description LIKE '6%' OR Description LIKE '7%' OR Description LIKE '9%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE (Description LIKE '0%' OR Description LIKE '1%' OR Description LIKE '2%' OR Description LIKE '3%' OR Description LIKE '4%'OR Description LIKE '5%' OR Description LIKE '6%' OR Description LIKE '7%' OR Description LIKE '9%') AND IsActive = 'true'", con);
                        }
                        break;
                    }

                case Utilities.SearchType.TypeAE:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'A%' OR Description LIKE 'B%' OR Description LIKE 'C%' OR Description LIKE 'D%' OR Description LIKE 'E%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE ( Description LIKE 'A%' OR Description LIKE 'B%' OR Description LIKE 'C%' OR Description LIKE 'D%' OR Description LIKE 'E%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeFJ:
                    {
                        if (ShowInactive)
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'F%' OR Description LIKE 'G%' OR Description LIKE 'H%' OR Description LIKE 'I%' OR Description LIKE 'J%'", con);
                        }
                        else
                        {
                            com = new SqlCommand("SELECT * FROM MinorWork WHERE ( Description LIKE 'F%' OR Description LIKE 'G%' OR Description LIKE 'H%' OR Description LIKE 'I%' OR Description LIKE 'J%') AND IsActive = 'true'", con);
                        }
                        break;
                    }
                case Utilities.SearchType.TypeKM:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'K%' OR Description LIKE 'L%' OR Description LIKE 'M%' OR Description LIKE 'N%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE (Description LIKE 'K%' OR Description LIKE 'L%' OR Description LIKE 'M%' OR Description LIKE 'N%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeOR:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'O%' OR Description LIKE 'P%' OR Description LIKE 'Q%' OR Description LIKE 'R%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE ( Description LIKE 'O%' OR Description LIKE 'P%' OR Description LIKE 'Q%' OR Description LIKE 'R%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeSV:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'S%' OR Description LIKE 'T%' OR Description LIKE 'U%' OR Description LIKE 'V%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE ( Description LIKE 'S%' OR Description LIKE 'T%' OR Description LIKE 'U%' OR Description LIKE 'V%') AND IsActive = 'true'", con);
                    }
                    break;
                case Utilities.SearchType.TypeWZ:
                    if (ShowInactive)
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE Description LIKE 'W%' OR Description LIKE 'X%' OR Description LIKE 'Y%' OR Description LIKE 'Z%'", con);
                    }
                    else
                    {
                        com = new SqlCommand("SELECT * FROM MinorWork WHERE ( Description LIKE 'W%' OR Description LIKE 'X%' OR Description LIKE 'Y%' OR Description LIKE 'Z%') AND IsActive = 'true'", con);
                    }
                    break;

            }

            adapt = new SqlDataAdapter(com);
            dtResult = new DataTable();
            adapt.Fill(dtResult);
            return dtResult;                       
            
        }
        public bool AddMinor(Minor MinorIn)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("INSERT INTO MinorWork VALUES (@PrimiseId, @Description, @EndDate, @NotesAction, @Directorate, @ContactID,@Status, @IsActive,@IsTBA,@EstimatedCost,@ActualCost,@AuthorisedDate,@AnticipatedCompletion,@ActualDate)", con);
            com.Parameters.Add("@PrimiseId", SqlDbType.Int);
            com.Parameters["@PrimiseId"].Value = MinorIn.PrimiseId;
            com.Parameters.Add("@Description", SqlDbType.NVarChar, 255);
            com.Parameters["@Description"].Value = MinorIn.Description;
            com.Parameters.Add("@EndDate", SqlDbType.Date);
            com.Parameters["@EndDate"].Value = MinorIn.EndDate;
            com.Parameters.Add("@NotesAction", SqlDbType.NVarChar, 255);
            com.Parameters["@NotesAction"].Value = MinorIn.NotesAction;
            com.Parameters.Add("@Directorate", SqlDbType.NVarChar, 255);
            com.Parameters["@Directorate"].Value = MinorIn.Directorate;
            com.Parameters.Add("@ContactID", SqlDbType.Int);
            com.Parameters["@ContactID"].Value = MinorIn.ContactId;
            com.Parameters.Add("@Status", SqlDbType.NVarChar,255);
            com.Parameters["@Status"].Value = MinorIn.Status;
            com.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
            com.Parameters["@Email"].Value = MinorIn.Status;
            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = MinorIn.IsActive;
            com.Parameters.Add("@IsTBA", SqlDbType.Bit);
            com.Parameters["@IsTBA"].Value = MinorIn.IsTBA;
            com.Parameters.Add("@EstimatedCost", SqlDbType.NVarChar,255);
            com.Parameters["@EstimatedCost"].Value = MinorIn.EstimatedCost;
            com.Parameters.Add("@ActualCost", SqlDbType.NVarChar,255);
            com.Parameters["@ActualCost"].Value = MinorIn.ActualCost;
            com.Parameters.Add("@AuthorisedDate", SqlDbType.Date);
            com.Parameters["@AuthorisedDate"].Value = MinorIn.AuthorisedDate;
            com.Parameters.Add("@AnticipatedCompletion", SqlDbType.Date);
            com.Parameters["@AnticipatedCompletion"].Value = MinorIn.AnticipatedCompletion;
            com.Parameters.Add("@ActualDate", SqlDbType.Date);
            com.Parameters["@ActualDate"].Value = MinorIn.ActualCompletionDate;
             

            return Convert.ToBoolean(com.ExecuteNonQuery());
        }
        public Minor GetById(int Id)
        {
            SqlCommand com = new SqlCommand("SELECT * FROM MinorWork WHERE MinorId = @id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = Id;
            SqlDataAdapter adapt = new SqlDataAdapter(com);
            DataTable dtResult = new DataTable();
            adapt.Fill(dtResult);
            Minor minor = new Minor();
            minor.Description = dtResult.Rows[0]["Description"].ToString();
            minor.EndDate = dtResult.Rows[0]["EndDate"].ToString();
            minor.NotesAction = dtResult.Rows[0]["NotesAction"].ToString();
            minor.Directorate = dtResult.Rows[0]["Directorate"].ToString();
            minor.ContactId = Convert.ToInt32(dtResult.Rows[0]["ContactId"].ToString());
            minor.Status=dtResult.Rows[0]["Status"].ToString();
            minor.IsActive = Convert.ToBoolean(dtResult.Rows[0]["IsActive"].ToString());
            minor.IsTBA = Convert.ToBoolean(dtResult.Rows[0]["IsTBA"].ToString());
            minor.EstimatedCost = dtResult.Rows[0]["EstimatedCost"].ToString();
            minor.ActualCost = dtResult.Rows[0]["ActualCost"].ToString();
            minor.AuthorisedDate = dtResult.Rows[0]["AuthorisedDate"].ToString();
            minor.AnticipatedCompletion=dtResult.Rows[0]["AnticipatedCompletion"].ToString();
            minor.ActualCompletionDate = dtResult.Rows[0]["ActualDate"].ToString();
            return minor;
        }
        public bool UpDateMinor(Minor MinorIn)
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            SqlCommand com = new SqlCommand("UPDATE MinorWork SET PrimiseId=@PrimiseId,Description= @Description,EndDate= @EndDate,NotesAction= @NotesAction, Directorate=@Directorate, ContactId=@ContactID,Status=@Status, IsActive=@IsActive,IsTBA=@IsTBA,EstimatedCost=@EstimatedCost,ActualCost=@ActualCost,AuthorisedDate=@AuthorisedDate,AnticipatedCompletion=@AnticipatedCompletion,ActualDate=@ActualDate WHERE MinorId=@id", con);
            com.Parameters.Add("@id", SqlDbType.Int);
            com.Parameters["@id"].Value = MinorIn.MinorId;
            com.Parameters.Add("@PrimiseId", SqlDbType.Int);
            com.Parameters["@PrimiseId"].Value = MinorIn.PrimiseId;
            com.Parameters.Add("@Description", SqlDbType.NVarChar, 255);
            com.Parameters["@Description"].Value = MinorIn.Description;
            com.Parameters.Add("@EndDate", SqlDbType.Date);
            com.Parameters["@EndDate"].Value = MinorIn.EndDate;
            com.Parameters.Add("@NotesAction", SqlDbType.NVarChar, 255);
            com.Parameters["@NotesAction"].Value = MinorIn.NotesAction;
            com.Parameters.Add("@Directorate", SqlDbType.NVarChar, 255);
            com.Parameters["@Directorate"].Value = MinorIn.Directorate;
            com.Parameters.Add("@ContactID", SqlDbType.Int);
            com.Parameters["@ContactID"].Value = MinorIn.ContactId;
            com.Parameters.Add("@Status", SqlDbType.NVarChar, 255);
            com.Parameters["@Status"].Value = MinorIn.Status;
            com.Parameters.Add("@Email", SqlDbType.NVarChar, 255);
            com.Parameters["@Email"].Value = MinorIn.Status;
            com.Parameters.Add("@IsActive", SqlDbType.Bit);
            com.Parameters["@IsActive"].Value = MinorIn.IsActive;
            com.Parameters.Add("@IsTBA", SqlDbType.Bit);
            com.Parameters["@IsTBA"].Value = MinorIn.IsTBA;
            com.Parameters.Add("@EstimatedCost", SqlDbType.NVarChar, 255);
            com.Parameters["@EstimatedCost"].Value = MinorIn.EstimatedCost;
            com.Parameters.Add("@ActualCost", SqlDbType.NVarChar, 255);
            com.Parameters["@ActualCost"].Value = MinorIn.ActualCost;
            com.Parameters.Add("@AuthorisedDate", SqlDbType.NVarChar, 255);
            com.Parameters["@AuthorisedDate"].Value = MinorIn.AuthorisedDate;
            com.Parameters.Add("@AnticipatedCompletion", SqlDbType.NVarChar, 255);
            com.Parameters["@AnticipatedCompletion"].Value = MinorIn.AnticipatedCompletion;
            com.Parameters.Add("@ActualDate", SqlDbType.NVarChar, 255);
            com.Parameters["@ActualDate"].Value = MinorIn.ActualCompletionDate;
            return Convert.ToBoolean(com.ExecuteNonQuery());
        }
    }
}